from datetime import datetime, timezone

from python_depend.mysql_pool import MySQLPool
from python_depend.tidb_data_query import fetch_all_stock_list
from python_depend.time_urtils import  get_current_date
from python_depend.workflow_api import work_flow_call

# ==============================
# 数据写入函数
# ==============================
def write_to_mysql(market, symbol, data):
    mysql_pool = MySQLPool.get_pool('feature_quote_data')
    if not data:
        print("没有数据需要保存")
        return
    values = []
    data_date = get_current_date()
    dt = datetime.strptime(data_date, "%Y-%m-%d")
    # 转为UTC时间戳（秒），再乘以1000得到毫秒
    timestamp_ms = int(dt.replace(tzinfo=timezone.utc).timestamp() * 1000)
    market_code = str(market) + ":" + symbol
    for radar_score in data:
        values.append((
            market_code,
            symbol,
            radar_score.get('technical_score', None),
            radar_score.get('technical_evaluation', ""),
            radar_score.get('analyst_score', None),
            radar_score.get('analyst_evaluation', ""),
            radar_score.get('fund_score', None),
            radar_score.get('fund_evaluation', ""),
            radar_score.get('fundamentals_score', None),
            radar_score.get('fundamentals_evaluation', ""),
            data_date,
            timestamp_ms

        ))
    delete_sql = f"DELETE FROM ods_stock_radar_score_snapshot WHERE market_code = %s"
    connection = None
    try:
        connection = mysql_pool.connection()
        with connection.cursor() as cursor:
            #删除快照数据
            cursor.execute(delete_sql, (market_code,))
            # 写入快照表
            cursor.executemany(get_upset_sql(), values)
        connection.commit()
    except Exception as e:
        print(f"写入失败: {e}")
    finally:
        if connection:
            try:
                connection.close()  # 归还到连接池
            except Exception as e:
                print("关闭连接失败", e)

def get_upset_sql():
    upsert_sql =  """
        INSERT INTO ods_stock_radar_score_snapshot (
            market_code, stock_code, technical_score, technical_evaluation,
            analyst_score, analyst_evaluation, fund_score, fund_evaluation,
            fundamentals_score, fundamentals_evaluation, data_date,
            create_time, update_time,record_time
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,now(), now(),%s
        ) ON DUPLICATE KEY UPDATE
            technical_score = VALUES(technical_score),
            technical_evaluation = VALUES(technical_evaluation),
            analyst_score = VALUES(analyst_score),
            analyst_evaluation = VALUES(analyst_evaluation),
            fund_score = VALUES(fund_score),
            fund_evaluation = VALUES(fund_evaluation),
            fundamentals_score = VALUES(fundamentals_score),
            fundamentals_evaluation = VALUES(fundamentals_evaluation),
            data_date = VALUES(data_date),
            update_time = VALUES(update_time)
        """
    return upsert_sql

def executor_data(market,code, error_code_list):
    input_variable_values = {
        "stockList":[code]
    }
    pipe_name = "827"
    data = work_flow_call(pipe_name, input_variable_values)
    if data is None or data.get('response') is None:
        print(f"数据异常，记录执行异常代码{code}")
        error_code_list.append(code)
    radar_data = data.get('response', {}).get('output', [{}])
    write_to_mysql(market, code, radar_data)


# ==============================
# 主执行函数
# ==============================
def execute_job():
    """主任务执行流程"""
    error_code_list = []
    print("开始执行任务")
    if not stock_list:
        print("股票列表为空，任务终止")
        return
    print("查询股票数量：" + str(len(stock_list)))
    # 2. 处理每只股票
    count = 0
    for stock in stock_list:
        count = count+1
        try:
            code = stock['code']
            market = stock['market']
            print(f"处理第{count}只股票: Code={code},")
            executor_data(market,code, error_code_list)
        except Exception as e:
            print("执行异常", e)
    if len(error_code_list) <= 100:
        print(f"任务执行完成")
    else:
        print(f"任务执行完成,执行失败代码:{str(error_code_list)}")
        # 存在异常代码，退出告警
        exit(1)


# ==============================
# 程序入口
# ==============================
if __name__ == "__main__":
    # 获取所有股票
    # stock_list = [{"code": "AAPL", "market": 185}]
    MySQLPool.initialize()
    stock_list = fetch_all_stock_list()
    execute_job()
    MySQLPool.close_all()

